package db;

/**
 * BankDatabase.java represents class that access and handles Database using SQL statements
 * 
 * @author XA9
 * 
 */

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class BankDatabase {

	/** variable representing class DbConnector.java */
	private DbConnector db;

	/**
	 * 
	 * BankDatabase constructor initialize variable as this.variable
	 * 
	 * */
	public BankDatabase() {
		this.db = DbConnector.getInstance();
	}

	/**
	 * 
	 * Method getAdmin of type Admin runs SQL SELECT statement and determines
	 * whether a user-specified password and loginId matches password and
	 * loginId in BankDatabase 
	 * 
	 * @param loginId
	 * @param password
	 * @return c
	 * 
	 * */
	private Admin getAdmin(int loginId, int password) {
		String query = "SELECT * FROM Admins WHERE LoginId = '" + loginId
				+ "' AND Password = '" + password + "'";
		Admin c = null;
		ResultSet rs;
		try {
			rs = db.doQuery(query);
			while (rs.next()) {
				c = new Admin(rs.getInt(1), rs.getInt(2), rs.getString(5),
						rs.getString(4), rs.getInt(3));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return c;
	}

	/**
	 * 
	 * Method getAccounts of type ArrayList (using Account.java as data object)
	 * select all values from accounts and returns them
	 * 
	 * @return accounts
	 * 
	 * */
	public ArrayList<Account> getAccounts() {
		String query = "SELECT * FROM ACCOUNTS ORDER BY LoginId";

		ArrayList<Account> accounts = new ArrayList<Account>();

		ResultSet rs;
		try {
			rs = db.doQuery(query);
			while (rs.next()) {
				Account account = new Account(rs.getInt(1), rs.getString(2),
						rs.getDouble(3), rs.getDouble(4), rs.getInt(5));
				accounts.add(account);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return accounts;
	}

	/**
	 * 
	 * Method getCustomers of type ArrayList (using Customer.java as data
	 * object) select all values from accounts and returns them
	 * 
	 * @return customers
	 * 
	 * */
	public ArrayList<Customer> getCustomers() {
		String query = "SELECT * FROM CUSTOMERS";

		ArrayList<Customer> customers = new ArrayList<Customer>();

		ResultSet rs;
		try {
			rs = db.doQuery(query);
			while (rs.next()) {
				Customer customer = new Customer(rs.getInt(1), rs.getInt(2),
						rs.getInt(3), rs.getString(4), rs.getString(5));
				customers.add(customer);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return customers;
	}

	/**
	 * 
	 * Method addCustomer insert user input values into BankDatabase using a SQL
	 * INSERT INTO statement
	 * 
	 * @param loginid
	 * @param password
	 * @param cpr
	 * @param firstname
	 * @param lastname
	 * 
	 * */
	public void addCustomer(String loginid, String password, String cpr,
			String firstname, String lastname) {
		String query = "INSERT INTO CUSTOMERS (LOGINID,PASSWORD,CPR,FIRSTNAME,LASTNAME) VALUES ('"
				+ loginid
				+ "','"
				+ password
				+ "','"
				+ cpr
				+ "','"
				+ firstname
				+ "','" + lastname + "')";

		try {
			db.doUpdate(query);

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 
	 * Method addAccount insert user input values into BankDatabase using a SQL
	 * INSERT INTO statement
	 * 
	 * @param accountnumber
	 * @param accountname
	 * @param balance
	 * @param rate
	 * @param loginid
	 * 
	 * */
	public void addAccount(String accountnumber, String accountname,
			String balance, String rate, String loginid) {
		String query = "INSERT INTO ACCOUNTS (AccountNumber,Account,Balance,Rate,LoginId) VALUES ('"
				+ accountnumber
				+ "','"
				+ accountname
				+ "','"
				+ balance
				+ "','"
				+ rate + "','" + loginid + "')";

		try {
			db.doUpdate(query);

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 
	 * Method addInterest adds interest to select account using SQL POW() and
	 * user input quart, representing number of quarters to add interest for
	 * 
	 * @param accountnumber
	 * @param quart
	 * 
	 * */
	public void addInterest(String accountNumber, String quart) {
		String query = "UPDATE accounts SET accounts.Balance = accounts.Balance + POW(accounts.rate,"
				+ "'"
				+ quart
				+ "')"
				+ "WHERE Accountnumber ='"
				+ accountNumber
				+ "'";

		try {
			db.doUpdate(query);

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 
	 * Method addInterest adds interest to all accounts using SQL POW() and user
	 * input quart, representing number of quarters to add interest for
	 * 
	 * @param accountnumber
	 * @param quart
	 * 
	 * */
	public void addInterestAll(String accountNumber, String quart) {
		String query = "UPDATE accounts SET accounts.Balance = accounts.Balance + POW(accounts.rate,"
				+ "'" + quart + "')";

		try {
			db.doUpdate(query);

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 
	 * Method deleteAccount deletes selected account using SQL DELETE
	 * 
	 * @param accountnumber
	 * 
	 * */
	public void deleteAccount(String accountnumber) {
		String query = "DELETE FROM ACCOUNTS WHERE ACCOUNTNUMBER ='"
				+ accountnumber + "'";

		try {
			db.doUpdate(query);

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 
	 * Method deleteAccount deletes selected account using SQL DELETE where
	 * loginId equals the selected
	 * 
	 * @param loginId
	 * 
	 * */
	public void deleteAccounts(String loginId) {
		String query = "DELETE FROM ACCOUNTS WHERE LOGINID = '" + loginId + "'";

		try {
			db.doUpdate(query);

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 
	 * Method deleteCustomer deletes selected customer using SQL DELETE where
	 * loginId equals the selected
	 * 
	 * @param loginId
	 * 
	 * */
	public void deleteCustomer(String loginId) {
		String query = "DELETE FROM CUSTOMERS WHERE LOGINID ='" + loginId + "'";

		try {
			db.doUpdate(query);

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 
	 * Method authenticateUser determines whether a user-specified loginId and
	 * password matches loginId and password in ArrayList Admin
	 * 
	 * @param userLoginId
	 * @param userPassword
	 * @return true/false
	 * 
	 * */
	public boolean authenticateUser(int userLoginId, int userPassword) {
		Admin userAdmin = getAdmin(userLoginId, userPassword);

		if (userAdmin != null)
			return true;
		else
			return false;
	}
}
